package Ms

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"strconv"
	"strings"
)

type constraintTypeMeS struct {
	NameB       string `json:"name"`
	ConstraintB string `json:"constraint"`
}
type indexColumnDistinctMeS struct {
	ColumnName     string `json:"columnName"`
	ColumnDistinct string `json:"columnDistinct"`
}

var intType = map[string]string{"NUMBER": "int", "INTEGER": "int", "FLOAT": "int", "REAL": "int", "DOUBLE": "int", "DECIMAL": "int", "BINARY_FLOAT": "int", "BINARY_DOUBLE": "int"}
var timeType = map[string]string{"DATE": "time", "TIMESTAMP": "time", "INTERVAL YEAR": "time", "INTERVAL DAY": "time"}
var stringType = map[string]string{"CHAR": "string", "VARCHAR2": "string", "NCHAR": "string", "NVARCHAR2": "string"}
var typeCost = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}

func (im IndexMe) Set(parameter string, value any) (err error) {
	return ref.UpdateStructJsonValue(im, parameter, value)
}
func (im IndexMe) Result(object string) global.Return {
	switch object {
	default:
		return global.Return{}
	}
}
func (im IndexMe) ConstraintType(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[constraintType]"
	var res []constraintTypeMeS
	var execSql []string
	for _, v := range []string{"ALL_CONSTRAINTS", "DBA_CONSTRAINTS"} {
		execSql = append(execSql, fmt.Sprintf("select "+
			"CO.INDEX_NAME as \"name\", \n"+
			"CO.CONSTRAINT_TYPE as \"constraint\" \n"+
			"from "+
			"%v \"CO\" "+
			"where OWNER IN ('%v') AND TABLE_NAME IN ('%v')",
			v,
			parameter.Object.Schema,
			parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("constraintType", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p = make(map[string]string)
	for _, v := range result.Result.([]map[string]any) {
		vv := mapNullActive(v)
		name, ok1 := vv["name"]
		constraint, ok2 := vv["constraint"]
		if ok1 && ok2 {
			p[fmt.Sprintf("%v", name)] = fmt.Sprintf("%v", constraint)
		}
		var dd = constraintTypeMeS{}
		if err = mapStringToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("constraintType", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}

type indexSubColumn struct {
	IndexSeq  string `json:"indexSeq"`
	ColumnSeq string `json:"columnSeq"`
	ColumnId  string `json:"columnId"`
}

func (im IndexMe) getIndexSubColumn(parameter parDef.Parameter, objectId string) (result global.Return, err error) {
	var event = "[getIndexSubColumn]"
	var res []indexSubColumn
	var execSql []string
	execSql = append(execSql, fmt.Sprintf("select \n"+
		"CAST ( index_id  AS VARCHAR ( 10 ) ) AS [indexSeq], \n"+
		"CAST ( index_column_id  AS VARCHAR ( 10 ) ) AS [columnSeq], \n"+
		"CAST ( column_id AS VARCHAR ( 10 ) ) AS [columnId] \n"+
		"from \n"+
		"sys.index_columns \n"+
		"where \n"+
		"object_id = ('%v') \n"+
		"order by index_id,index_column_id", objectId))
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("getIndexSubColumn", err)
		return
	}
	if result.Result == nil {
		return
	}
	for _, v := range result.Result.([]map[string]interface{}) {
		vv := mapNullActive(v)
		var dd = indexSubColumn{}
		if err = mapStringToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("getIndexSubColumn", err)
			return
		}
		res = append(res, dd)
	}
	result.Result = res
	return
}
func (im IndexMe) indexCardinality(parameter parDef.Parameter, objectId, indexId string) (result global.Return, err error) {
	var event = "[indexCardinality]"
	var execSql = []string{fmt.Sprintf("SELECT "+
		"CAST ( "+
		"	CASE \n"+
		"    	WHEN SUM(rows*p.distinct_range_rows) = 0 THEN NULL -- 避免除以零错误\n"+
		"        	ELSE ROUND(CAST(SUM(p.DISTINCT_RANGE_ROWS)* 100 AS FLOAT) / CAST(SUM(rows*p.distinct_range_rows) AS FLOAT), 0) \n"+
		"    	END AS VARCHAR ( 10 ) )"+
		"AS [cardinality]"+
		"FROM \n"+
		"(SELECT \n"+
		"    CASE \n"+
		"        WHEN sps.DISTINCT_RANGE_ROWS = 0 THEN 1 \n"+
		"        ELSE sps.DISTINCT_RANGE_ROWS \n"+
		"    END AS DISTINCT_RANGE_ROWS,\n"+
		"\t\tequal_rows as rows\n"+
		"FROM \n"+
		"    sys.stats s\n"+
		"CROSS APPLY \n"+
		"    sys.dm_db_stats_histogram(s.object_id, s.stats_id) sps\n"+
		"WHERE \n"+
		"    s.object_id = %v AND s.stats_id = %v \n"+
		") P ", objectId, indexId)}
	if result, err = forExecQuerySql(event, parameter, ETL.String, execSql); err != nil {
		err = ref.ErrAddPrintf("indexCardinality", err)
		return
	}
	if result.Result == nil || strings.EqualFold(fmt.Sprintf("%v", result.Result), "<nil>") {
		result.Result = 0
		return
	}
	if result.Result, err = stringAnyToInt64(result.Result); err != nil {
		err = ref.ErrAddPrintf("indexCardinality", err)
		return
	}
	return
}
func (im IndexMe) getObjectId(parameter parDef.Parameter) (result global.Return, err error) {
	var event string
	var execSql = []string{fmt.Sprintf("select object_id('%v.%v') as [objectId]", parameter.Object.Schema, parameter.Object.Table)}
	if result, err = forExecQuerySql(event, parameter, ETL.String, execSql); err != nil {
		err = ref.ErrAddPrintf("getObjectId", err)
		return
	}
	return
}

type indexNameG struct {
	SchemaName  string
	TableName   string
	Name        string `json:"name"`
	IndexSeq    string `json:"seq"`
	NoUnique    string `json:"nonUnique"`
	ColumnKey   string `json:"columnKey"`
	Cardinality int64  `json:"cardinality"`
}

func (im IndexMe) getIndexName(parameter parDef.Parameter, objectId string) (result global.Return, err error) {
	var event = "[getIndexName]"
	var res []indexNameG
	execSql := []string{fmt.Sprintf("select "+
		"i.name AS [name],\n"+
		"CAST ( i.index_id  AS VARCHAR ( 10 ) ) AS [seq],\n"+
		"CAST ( i.is_unique  AS VARCHAR ( 10 ) ) AS [nonUnique],\n"+
		"CAST (i.is_primary_key AS VARCHAR ( 10 ) ) AS [columnKey]\n"+
		" from \n"+
		"sys.indexes i \n"+
		"where \n"+
		"object_id = ('%v') \n"+
		"and is_hypothetical = 0 and is_disabled = 0 AND type > 0 \n"+
		"order by i.name", objectId)}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("getIndexName", err)
		return
	}
	if result.Result == nil {
		return
	}
	for _, v := range result.Result.([]map[string]any) {
		v1 := mapNullActive(v)
		var s indexNameG
		if err = ref.MapToStruct(v1, &s); err != nil {
			err = ref.ErrAddPrintf("getIndexName", err)
			return
		}
		s.SchemaName = parameter.Object.Schema
		s.TableName = parameter.Object.Table
		res = append(res, s)
	}
	result.Result = res
	return
}
func (im IndexMe) nameColumnBase(parameter parDef.Parameter) (colMeta, columnMeta, indexMe global.Return, err error) {
	var res global.Return
	var objectId string
	cm := ColumnMe{}
	if colMeta, err = cm.Meta(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	parameter1 := parameter
	parameter1.Meta = colMeta.Result
	if colMeta, err = cm.MetaMap(parameter1); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if res, err = im.getObjectId(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	} else {
		objectId = fmt.Sprintf("%v", res.Result)
	}
	if columnMeta, err = im.getIndexSubColumn(parameter, objectId); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	var indexG, newIndexG []indexNameG
	if res, err = im.getIndexName(parameter, objectId); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	} else {
		switch res.Result.(type) {
		case []indexNameG:
			indexG = res.Result.([]indexNameG)
		}
	}
	for _, v := range indexG {
		var cardinality global.Return
		if cardinality, err = im.indexCardinality(parameter, objectId, v.IndexSeq); err != nil {
			err = ref.ErrAddPrintf("nameColumnBase", err)
			return
		}
		switch cardinality.Result.(type) {
		case int64:
			v.Cardinality = cardinality.Result.(int64)
		}
		newIndexG = append(newIndexG, v)
	}
	indexMe.Result = newIndexG
	return
}

func (im IndexMe) indexColumnBase(colMeta, indexColumnMeta global.Return, indexSeq string) (res []parDef.IdxSubColumnMe) {
	if indexColumnMeta.Result == nil {
		return
	}
	for _, column := range indexColumnMeta.Result.([]indexSubColumn) {
		if indexSeq == column.IndexSeq {
			var dd = parDef.IdxSubColumnMe{}
			for _, columnInfo := range colMeta.Result.([]parDef.ColMetaMapS) {
				if columnInfo.ColumnSeq == column.ColumnSeq {
					dd.ColumnName = columnInfo.ColumnName
					dd.IndexSeq = fmt.Sprintf("%v", column.ColumnSeq)
					dd.ColumnSeq = fmt.Sprintf("%v", columnInfo.ColumnSeq)
					dd.ColumnNull = columnInfo.ColNullMap
					dd.ColumnType = columnInfo.Type.ColTypeMap
					dd.Collation = columnInfo.CollationName
					res = append(res, dd)
					break
				}
			}
		}
	}
	return
}

func (im IndexMe) newColumnInit(allIndex global.Return) (res []parDef.IndexColumnMe) {
	if allIndex.Result == nil {
		return
	}
	for _, v := range allIndex.Result.([]indexNameG) {
		res = append(res, parDef.IndexColumnMe{
			Schema:        v.SchemaName,
			Table:         v.TableName,
			IndexName:     v.Name,
			IndexCategory: "local",
			NonUnique:     v.NoUnique,
			Cardinality:   v.Cardinality,
			IndexSeq:      v.IndexSeq,
		})
	}
	return
}
func (im IndexMe) columnKey(vv parDef.IndexColumnMe) (result parDef.IndexColumnMe) {
	result = vv
	switch strings.TrimSpace(vv.ColumnKey) {
	case "1":
		result.ColumnKey = "P"
		result.IndexName = "PRIMARY"
	default:
		switch strings.TrimSpace(vv.NonUnique) {
		case "1":
			result.ColumnKey = "U"
		default:
			result.ColumnKey = "I"
		}
	}
	return
}

func (im IndexMe) ColumnDistinct(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[columnDistinct]"
	var execSql []string
	var res []indexColumnDistinctMeS
	for _, v := range []string{"all_tab_col_statistics", "dba_tab_col_statistics"} {
		execSql = append(execSql, fmt.Sprintf("SELECT "+
			"COLUMN_NAME AS \"columnName\","+
			"NUM_DISTINCT AS \"columnDistinct\" "+
			"FROM "+
			"%v "+
			"WHERE "+
			"OWNER = '%v' AND table_name = '%v'", v, parameter.Object.Schema, parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("columnDistinct", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p = make(map[string]any)
	for _, v := range result.Result.([]map[string]any) {
		vv := mapNullActive(v)
		columnName, ok1 := vv["columnName"]
		columnDistinct, ok2 := vv["columnDistinct"]
		if ok1 && ok2 {
			p[fmt.Sprintf("%v", columnName)] = columnDistinct
		}
		var dd = indexColumnDistinctMeS{}
		if err = ref.MapToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("columnDistinct", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}
func (im IndexMe) NameColumn(parameter parDef.Parameter) (result global.Return, err error) {
	var colMeta, indexColumnMeta, allIndex global.Return
	if colMeta, indexColumnMeta, allIndex, err = im.nameColumnBase(parameter); err != nil {
		err = ref.ErrAddPrintf("NameColumn", err)
		return
	}
	res := im.newColumnInit(allIndex)
	var resStr []parDef.IndexColumnMe
	for _, vv := range res {
		var newNameColumnMe = vv
		vv.ColumnMate = im.indexColumnBase(colMeta, indexColumnMeta, vv.IndexSeq)
		if len(vv.ColumnMate) == 0 {
			continue
		}
		newNameColumnMe = im.columnKey(vv)
		resStr = append(resStr, newNameColumnMe)
	}
	result.Result = resStr
	return
}

// 按照索引离散度倒序排序（离散度最多两个列）
func (im IndexMe) cardinalityReverseSort(IIData []parDef.IndexColumnMe, Sum int64) (result []string) {
	var cardinality = make(map[string]int64)
	for _, v := range IIData {
		if Sum > 0 && v.Cardinality > 0 && v.Cardinality*100/Sum > 30 {
			cardinality[v.IndexName] = v.Cardinality
		}
	}
	result = ref.MapValueReverseSort(cardinality)
	return
}
func specifyIndexNameToNameColumnMe(specifyIndex string, IIData []parDef.IndexColumnMe) (p parDef.IndexColumnMe) {
	for _, vv := range IIData {
		if strings.EqualFold(specifyIndex, vv.IndexName) {
			p = vv
			return
		}
	}
	return
}
func compoundIndexColumnHistogram(columnHis []parDef.IdxSubColumnMe, cDistinctMeta any) (hist []int64) {
	var err error
	for k, vv := range columnHis {
		if k > 1 {
			continue
		}
		if colHist, ok := cDistinctMeta.(map[string]any)[vv.ColumnName]; ok {
			var hv int64
			if hv, err = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", colHist)), 10, 64); err != nil {
				hist = append(hist, 0)
			} else {
				hist = append(hist, hv)
			}
		}
	}
	return
}
func (im IndexMe) columnCardinalityStandard(hist []int64, Sum int64) bool {
	for _, vv := range hist {
		if vv*100/Sum > 30 {
			return true
		}
	}
	return false
}

// 比较单列和两个列的直方图，选择合适的
func (im IndexMe) columnHistogram(parameter parDef.Parameter, IIData []parDef.IndexColumnMe, Sum int64) (result []string, err error) {
	var columnDistinctMeta global.Return
	if columnDistinctMeta, err = im.ColumnDistinct(parameter); err != nil {
		err = ref.ErrAddPrintf("columnHistogram", err)
		return
	}
	if columnDistinctMeta.Result == nil {
		return
	}
	var cost = make(map[string]any)
	for _, v := range IIData {
		hist := compoundIndexColumnHistogram(v.ColumnMate, columnDistinctMeta.Result)
		switch {
		case len(hist) == 1 && hist[0] > 0 && im.columnCardinalityStandard(hist, Sum):
			cost[v.IndexName] = math.Round((float64(Sum)/float64(hist[0]))*100) / 100
		case len(hist) > 1 && hist[0] > 0 && hist[1] > 0 && im.columnCardinalityStandard(hist, Sum):
			var re, factor float64 = 0, 1
			for _, h := range hist {
				re = factor * math.Round((float64(Sum)/float64(h))*100) / 100
				factor = re
			}
			cost[v.IndexName] = re
		}
	}
	result = ref.MapValueSort(cost)
	return
}

func typeSelection(c parDef.IdxSubColumnMe) (cost int) {
	for _, v := range []map[string]string{intType, timeType, stringType} {
		if t, ok := v[strings.ToUpper(strings.Split(c.ColumnType, "(")[0])]; ok {
			return typeCost[t]
		}
	}
	return 100
}
func specifyIndexColumnTypeCost(IIData []parDef.IdxSubColumnMe) (cost int) {
	for _, v := range IIData {
		cost += typeSelection(v)
	}
	return
}

func (im IndexMe) typeScreening(screeningIndex []string, IIData []parDef.IndexColumnMe) (result string) {
	var indexTypeCost = make(map[string]any)
	for _, v := range screeningIndex {
		p := specifyIndexNameToNameColumnMe(v, IIData)
		indexTypeCost[v] = specifyIndexColumnTypeCost(p.ColumnMate)
	}
	res := ref.MapValueSort(indexTypeCost)
	if len(res) >= 1 {
		result = res[0]
	}
	return
}
func (im IndexMe) KeyChoose(parameter parDef.Parameter) (result global.Return, err error) {
	var res, indexColumnQueryData global.Return
	c := SumF{}
	if res, err = c.Rows(parameter); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if res.Result == nil {
		return
	}
	Sum := res.Result.(int64)
	if indexColumnQueryData, err = im.NameColumn(parameter); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if indexColumnQueryData.Result == nil {
		return
	}
	var IIData = indexColumnQueryData.Result.([]parDef.IndexColumnMe)
	var screeningIndex []string
	if screeningIndex, err = im.columnHistogram(parameter, IIData, Sum); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if len(screeningIndex) == 0 {
		screeningIndex = im.cardinalityReverseSort(IIData, Sum)
	}
	chooseIndex := im.typeScreening(screeningIndex, IIData)
	for _, v := range IIData {
		if strings.EqualFold(v.IndexName, chooseIndex) {
			result.Result = v
			return
		}
	}
	return
}

func (im IndexMe) NameToColumnClip(parameter parDef.Parameter) (result map[string][]string, err error) {
	var metaBase MetaBaseInitResult
	result = make(map[string][]string)
	if parameter.Meta == nil {
		return
	}
	if metaBase, err = metaBaseInit(parameter.Meta); err != nil {
		err = ref.ErrAddPrintf("NameToColumnClip", err)
		return
	}
	for _, indexCol := range metaBase.indexMeta {
		result[strings.ToUpper(fmt.Sprintf("%v@%v", indexCol.IndexName, indexCol.ColumnKey))] = func() (res []string) {
			for _, p := range indexCol.ColumnMate {
				res = append(res, strings.ToUpper(p.ColumnName))
			}
			return
		}()
	}
	return
}
